﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace SYSoft.Comm.Utils
{
    public class ExcelHelper
    {
        /// <summary>
        /// 将 DataTable 转换为 Excel
        /// 需在 nuget 安装 DotNetCore.NPOI
        /// </summary>
        /// <param name="table">数据表</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static byte[] DataTableToExcel(DataTable table, string title = null, string sheetName = "Sheet")
        {
            try
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet(sheetName);
                int cellsIndex = 0;
                // 标题
                if (!string.IsNullOrEmpty(title))
                {
                    // 填充数据
                    IRow cellsTitle = sheet.CreateRow(0);
                    cellsTitle.CreateCell(0).SetCellValue(title);
                    // 合并单元格
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, table.Columns.Count - 1));
                    cellsIndex = 2;
                }
                // 填充表头
                IRow cellsHeader = sheet.CreateRow(cellsIndex);
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    cellsHeader.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                }
                // 填充数据
                cellsIndex += 1;
                foreach (DataRow dr in table.Rows)
                {
                    IRow row = sheet.CreateRow(cellsIndex);
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(dr[i].ToString());
                    }
                    cellsIndex++;
                }
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    buffer = ms.GetBuffer();
                    ms.Close();
                }
                return buffer;
            }
            catch (Exception ex)
            {
                return null;
            }
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="ColumnName">第一行标题名称</param>
        /// <param name="FieldName">sql 语句字段</param>
        /// <param name="title"></param>
        /// <returns></returns>
        public static byte[] TableToExcel(DataTable table,List<string> ColumnName, List<string> FieldName ,string title = null)
        {
            try
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet");
                int cellsIndex = 0;
                // 标题
                if (!string.IsNullOrEmpty(title))
                {
                    // 填充数据
                    IRow cellsTitle = sheet.CreateRow(0);
                    cellsTitle.CreateCell(0).SetCellValue(title);
                    // 合并单元格
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, table.Columns.Count - 1));
                    cellsIndex = 2;
                }
                // 填充表头
                IRow cellsHeader = sheet.CreateRow(cellsIndex);
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    cellsHeader.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                }
                // 填充数据
                cellsIndex += 1;
                foreach (DataRow dr in table.Rows)
                {
                    IRow row = sheet.CreateRow(cellsIndex);
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(dr[i].ToString());
                    }
                    cellsIndex++;
                }
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    buffer = ms.GetBuffer();
                    ms.Close();
                }
                return buffer;
            }
            catch (Exception ex)
            {
                return null;
            }
        }



        /// <summary>
        /// 将 DataTable 转换为 CSV
        /// </summary>
        /// <param name="table">数据表</param>
        /// <returns></returns>
        public static Stream DataTableToCsv(DataTable table)
        {
            try
            {
                StringBuilder builder = new StringBuilder();
                DataColumn column;
                int iColCount = table.Columns.Count;
                // 处理表头
                for (int i = 0; i < iColCount; i++)
                {
                    if (i != 0) builder.Append(",");
                    builder.Append("\"" + table.Columns[i].ColumnName + "\"");
                }
                builder.AppendLine();
                // 处理内容
                foreach (DataRow row in table.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {
                        column = table.Columns[i];
                        if (i != 0) builder.Append(",");
                        if (Convert.IsDBNull(row[column])) builder.Append("\"\"");
                        //else if (row[column].ToString().StartsWith("0")) builder.Append("\"'" + row[column].ToString() + "\"");
                        else builder.Append("\"" + row[column].ToString() + "\"");
                    }
                    builder.AppendLine();
                }
                byte[] bytes = Encoding.GetEncoding("GB2312").GetBytes(builder.ToString());
                Stream stream = new MemoryStream(bytes);
                return stream;
            }
            catch (Exception)
            {
                return null;
            }
        }

    }
}
